package com.itheima.store.dao.impl;


import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.itheima.store.dao.BookBorrowDao;
import com.itheima.store.domain.Book;
import com.itheima.store.domain.BookBorrow;
import com.itheima.store.utils.JDBCUtils;

public class BookBorrowDaoImpl implements BookBorrowDao {

	@Override
	public String addBookBorrow(BookBorrow bb) throws SQLException{
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql ="insert into book_borrowing values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Object[] params = { bb.getBookBorrowing_id(),bb.getUser_id(),bb.getUser_name(),bb.getBooks_id(),bb.getBooks_name(),
							bb.getBook_number(),bb.getApplicationTime(),bb.getConfirmationTime(),bb.getExpeReturnTime(),bb.getActuReturnTime(),
							bb.getIsRenew(),bb.getRenewDays(),bb.getBstatus(),bb.getTotal(),bb.getBook_image(),
							bb.getBook_author(),bb.getInventory(),0};
		int flag = queryRunner.update(sql, params);
		if(flag == 1){
			return "1";
		}else{
			return "0";
		}
	}

	@Override
	public Integer findCountByUserId(String user_id) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select count(*) from book_borrowing where user_id = ? and bstatus = ?";
		Long count = (Long) queryRunner.query(sql, new ScalarHandler(), user_id,3);
		return count.intValue();
	}

	@Override
	public List<BookBorrow> findPageByUserId(String user_id, int begin, Integer pageSize) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select * from book_borrowing where user_id = ?  and bstatus = ? order by applicationTime desc limit ?,?";
		List<BookBorrow> list = queryRunner.query(sql, new BeanListHandler<BookBorrow>(BookBorrow.class), user_id,3,begin,pageSize);
		return list;
	}

	@Override
	public void removeCart(String bookBorrowing_id) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "delete from book_borrowing where bookBorrowing_id =?";
		queryRunner.update(sql, bookBorrowing_id);
		
	}

	@Override
	public BookBorrow findAppBybookBorrId(String bookBorrowing_id) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select * from book_borrowing where bookBorrowing_id =?";
		BookBorrow bb = queryRunner.query(sql, new BeanHandler<BookBorrow>(BookBorrow.class), bookBorrowing_id);
		return bb;
	}

	@Override
	public void updateBookBorrow(BookBorrow bb) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "update book_borrowing set user_id=?,user_name=?,books_id=?,books_name= ?,applicationTime=?,confirmationTime=?,"
				+ "expeReturnTime=?,actuReturnTime=?,isRenew=?,renewDays=?,bstatus=?,total=?book_image=?book_author=?,inventory=?  where bookBorrowing_id = ?";
		Object[] params = { bb.getUser_id(),bb.getUser_name(),bb.getBooks_id(),bb.getBooks_name(),
				bb.getApplicationTime(),bb.getConfirmationTime(),bb.getExpeReturnTime(),bb.getActuReturnTime(),
				bb.getIsRenew(),bb.getRenewDays(),bb.getBstatus(),bb.getTotal(),bb.getBook_image(),
				bb.getBook_author(),bb.getInventory(),bb.getBookBorrowing_id()};
		queryRunner.update(sql, params);
		
	}

	@Override
	public void updateBookBorrow(String bookBorrowing_id, String total) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "update book_borrowing set total=?  where bookBorrowing_id = ?";
		Object[] params = { total,bookBorrowing_id };
		queryRunner.update(sql, params);
		
	}

	@Override
	public void deleteBookBorrAllApp(String user_id) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "delete from book_borrowing where user_id =? and bstatus = ?";
		queryRunner.update(sql, user_id,3);
		
	}

	@Override
	public List findAppByUserId(String user_id) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select * from book_borrowing where user_id = ?  and bstatus = ? order by applicationTime desc ";
		List<BookBorrow> list = queryRunner.query(sql, new BeanListHandler<BookBorrow>(BookBorrow.class), user_id,3);
		return list;
	}

	@Override
	public Integer findCount(Integer bstatus) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql ="";
		if(bstatus ==1){
			//sql = "select count(*) from book_borrowing where  bstatus in (0,?) and expeReturnTime < CURRENT_TIMESTAMP()";
			 sql = "SELECT count(*) FROM (SELECT * ,DATE_ADD(expeReturnTime,INTERVAL renewDays DAY) AS expeReturnTimes  FROM book_borrowing WHERE   bstatus IN (0,?) AND confirmationTime IS NOT NULL) a WHERE a.expeReturnTimes < CURRENT_TIMESTAMP()";
					
		}else{
			sql = "select count(*) from book_borrowing where  bstatus = ?";
		}
		
		Long count = (Long) queryRunner.query(sql, new ScalarHandler(), bstatus);
		return count.intValue();
	}

	@Override
	public List<BookBorrow> findPageByStatus(Integer bstatus, int begin, Integer pageSize) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql ="";
		if(bstatus==4){
			 sql = "select * from book_borrowing where   bstatus = ? order by applicationTime desc limit ?,?";
		}else if(bstatus==0){
			 sql = "select * from book_borrowing where   bstatus IN (?,1) order by confirmationTime desc limit ?,?";
		}else if(bstatus==1){
			 sql = "SELECT * FROM (SELECT * ,DATE_ADD(expeReturnTime,INTERVAL renewDays DAY) AS expeReturnTimes  FROM book_borrowing WHERE   bstatus IN (0,?) AND confirmationTime IS NOT NULL) a WHERE a.expeReturnTimes < CURRENT_TIMESTAMP()"
					 +" order by a.expeReturnTimes  limit ?,?";
		}else if(bstatus==2){
			 sql = "select *  from book_borrowing where   bstatus =?   order by actuReturnTime desc limit ?,?";

		}
		List<BookBorrow> list = queryRunner.query(sql, new BeanListHandler<BookBorrow>(BookBorrow.class), bstatus,begin,pageSize);
		return list;

	}

	@Override
	public void updateBookBorrowStatus(String bookBorrowing_id,String flag) throws SQLException {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date confirmationTime = null;
		Date actuReturnTime = null;
		String sql ="";
	
		try {
			confirmationTime = sdf.parse(sdf.format(Calendar.getInstance().getTime()));
			actuReturnTime = sdf.parse(sdf.format(Calendar.getInstance().getTime()));
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		if("0".equals(flag)){
			 sql = "update book_borrowing set bstatus=?,confirmationTime=?  where bookBorrowing_id = ?";
			 Object[] params = { 0,confirmationTime,bookBorrowing_id };
			 queryRunner.update(sql, params);
		}else if("2".equals(flag)){
			 sql = "update book_borrowing set bstatus=?,confirmationTime=?  where bookBorrowing_id = ?";
			 Object[] params = { 5,confirmationTime,bookBorrowing_id };
			 queryRunner.update(sql, params);
			 String seleSQl = "select * from book_borrowing where bookBorrowing_id = ?";
				
				List<BookBorrow> list = queryRunner.query(seleSQl, new BeanListHandler<BookBorrow>(BookBorrow.class), bookBorrowing_id);
				String book_id = list.get(0).getBooks_id();
				Integer total = list.get(0).getTotal();
				
				updateBookTotal(book_id,total,2);
		}else if("3".equals(flag)){
			 sql = "update book_borrowing set bstatus=? where bookBorrowing_id = ?";
			 Object[] params = { 6,bookBorrowing_id };
			 queryRunner.update(sql, params);
			 String seleSQl = "select * from book_borrowing where bookBorrowing_id = ?";
				
				List<BookBorrow> list = queryRunner.query(seleSQl, new BeanListHandler<BookBorrow>(BookBorrow.class), bookBorrowing_id);
				String book_id = list.get(0).getBooks_id();
				Integer total = list.get(0).getTotal();
				
				updateBookTotal(book_id,total,2);
		}else{
		
			sql = "update book_borrowing set bstatus=?,actuReturnTime=?  where bookBorrowing_id = ?";
			Object[] params = { 2,actuReturnTime,bookBorrowing_id };
			queryRunner.update(sql, params);
			String seleSQl = "select * from book_borrowing where bookBorrowing_id = ?";
			
			List<BookBorrow> list = queryRunner.query(seleSQl, new BeanListHandler<BookBorrow>(BookBorrow.class), bookBorrowing_id);
			String book_id = list.get(0).getBooks_id();
			Integer total = list.get(0).getTotal();
			
			updateBookTotal(book_id,total,0);
		}
		
		
		
	}
	
	
	
	@Override
	public List<BookBorrow> findByOid(String oids) throws SQLException {
		String[] ids = oids.split(",");
		/*String  bookBorrowing_id = "";
		for(int i=0;i<ids.length;i++){
			bookBorrowing_id = bookBorrowing_id+"'"+ids[i]+"',";
		}
		if(bookBorrowing_id.endsWith(",")){
			bookBorrowing_id = bookBorrowing_id.substring(0, bookBorrowing_id.length()-1);
		}*/
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		StringBuilder sql = new StringBuilder();
		Map params = null;
		 sql.append( "select * from book_borrowing where  1=1 "); 
		sql.append("AND bookBorrowing_id").append(" IN (");
        for (int i = 0; i < ids.length; i++) {
            if (i == ids.length - 1) {
                sql.append("?");
            } else {
                sql.append("?,");
            }
           // params.put( "i", ids[i]);
        }
        sql.append(") ");
		List<BookBorrow> list = queryRunner.query(sql.toString(), new BeanListHandler<BookBorrow>(BookBorrow.class),ids);
	
		return list;
	}

	@Override
	public void updateRenewDays(String bookBorrowing_id, String renewDays) throws SQLException {
	
			QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
			String sql = "update book_borrowing set renewDays=?,isRenew=?,bstatus=? where bookBorrowing_id = ?";
			Object[] params = { renewDays,1,1,bookBorrowing_id };
			queryRunner.update(sql, params);
			
	
	}

	@Override
	public BookBorrow findByBookId(String books_id, String user_id) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "select * from book_borrowing where books_id =? and user_id =? and bstatus =?";
		BookBorrow bb = queryRunner.query(sql, new BeanHandler<BookBorrow>(BookBorrow.class), books_id,user_id,3);
		return bb;
	}

	@Override
	public void updateTotal(String bookBorrowing_id, Integer total, Date expeReturnTime) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "update book_borrowing set total=? ,expeReturnTime=? where bookBorrowing_id = ?";
		Object[] params = { total,expeReturnTime,bookBorrowing_id };
		queryRunner.update(sql, params);
	}

	@Override
	public void updateBookTotal(String book_id, Integer tots,Integer flag) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		String sql = "";
		String sql2 = "";
		if(flag==1){
			 sql = "update book set inventory=?  where book_id = ?";
			 sql2 = "update book_borrowing set inventory=?  where bstatus =? and books_id = ?";
		}else{
			 sql = "update book set inventory=inventory+?  where book_id = ?";
			 sql2 = "update book_borrowing set inventory=inventory+?  where bstatus =? and books_id = ?";
		}
		
		Object[] params = { tots,book_id};
		Object[] params2 = { tots,3,book_id};
		queryRunner.update(sql, params);
		queryRunner.update(sql2,params2);
		
	}

}
